from sqlalchemy import Column, Integer, String, create_engine, DateTime, ForeignKey, UniqueConstraint
from sqlalchemy.ext.declarative import declarative_base
import datetime

from sqlalchemy.orm import relationship

Base = declarative_base()


class Classes(Base):
    __tablename__ = 'classes'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32), nullable=False, unique=True)


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(32), nullable=False, index=True)
    password = Column(String(64), nullable=False)
    ctime = Column(DateTime, default=datetime.datetime.now)
    class_id = Column(Integer, ForeignKey('classes.id'))

    cls = relationship('Classes', backref='stus')


class Hobby(Base):
    __tablename__ = 'hobby'
    id = Column(Integer, primary_key=True)
    caption = Column(String(50), default='篮球')


class Student2Hobby(Base):
    __tablename__ = 'student2hobby'
    id = Column(Integer, primary_key=True, autoincrement=True)
    student_id = Column(Integer, ForeignKey('student.id'))
    hobby_id = Column(Integer, ForeignKey('hobby.id'))

    __table_args__ = (
        UniqueConstraint('student_id', 'hobby_id', name='uix_student_id_hobby_id'),
        # Index('ix_id_name', 'name', 'email'),
    )


def init_db():
    engine = create_engine("mysql+pymysql://root:qaz3357375@127.0.0.1:3306/mydb?charset=utf8", max_overflow=5)
    Base.metadata.create_all(engine)  # 创建表


def drop_db():
    engine = create_engine("mysql+pymysql://root:qaz3357375@127.0.0.1:3306/mydb?charset=utf8", max_overflow=5)
    Base.metadata.drop_all(engine)  # 删除表

# 寻找Base的所有子类，按照子类的结构在数据库中生成对应的数据表信息


if __name__ == '__main__':
    init_db()
    # drop_db()
    pass
